package com.xhrd.generator.provider.db.sql.model;

import java.util.Iterator;
import java.util.LinkedHashSet;

import com.xhrd.generator.provider.db.sql.SqlFactory;
import com.xhrd.generator.provider.db.table.model.Column;
import com.xhrd.generator.provider.db.table.model.Table;
import com.xhrd.generator.util.StringHelper;
import com.xhrd.generator.util.sqlparse.SqlParseHelper;

/**
 * 用于生成代码的Sql对象.对应数据库的sql语句
 * 使用SqlFactory.parseSql()生成 <br />
 *
 * SQL参数同时支持以下几种语法
 * <pre>
 * hibernate: :username,
 * ibatis2: #username#,$usename$,
 * mybatis(or ibatis3): #{username},${username}
 * </pre>
 * SQL对象创建示例：
 * <pre>
 * Sql sql = new SqlFactory().parseSql("select * from user_info where username=#username# and password=#password#");
 * </pre>
 *
 * @see SqlFactory
 * @author badqiu
 *
 */
public class Sql {
    public static String MULTIPLICITY_ONE = "one";
    public static String MULTIPLICITY_MANY = "many";
    public static String MULTIPLICITY_PAGING = "paging";

    String tableSqlName = null; //是否需要
    String operation = null;
    String resultClass;
    String parameterClass;
    String remarks;

    String multiplicity = "many"; // many or one or paging
    boolean paging = false; // 是否分页查询
    String sqlmap; //for ibatis and ibatis3

    LinkedHashSet<Column> columns = new LinkedHashSet<Column>();
    LinkedHashSet<SqlParameter> params = new LinkedHashSet<SqlParameter>();

    String sourceSql; // source sql
    String executeSql;

    public Sql() {
    }

    public Sql(Sql sql) {
        this.tableSqlName = sql.tableSqlName;

        this.operation = sql.operation;
        this.parameterClass = sql.parameterClass;
        this.resultClass = sql.resultClass;
        this.multiplicity = sql.multiplicity;

        this.columns = sql.columns;
        this.params = sql.params;
        this.sourceSql = sql.sourceSql;
        this.executeSql = sql.executeSql;
        this.remarks = sql.remarks;
    }

    public boolean isColumnsInSameTable() {
        //FIXME 还要增加表的列数与columns是否相等,才可以为select 生成 include语句
        if(columns == null || columns.isEmpty()) return false;
        if(columns.size() == 1 && columns.iterator().next().getTable() != null) return true;
        String preTableName = columns.iterator().next().getSqlName();
        for(Column c :columns) {
            Table table = c.getTable();
            if(table == null) {
                return false;
            }
            if(preTableName.equalsIgnoreCase(table.getSqlName())) {
                continue;
            }
        }
        return true;
    }

    /**
     * 得到select查询返回的resultClass,可以通过setResultClass()自定义，如果没有自定义则为你自动生成<br />
     * resultClass可以为com.company.User的完全路径
     * 示例:
     * <pre>
     * select count(*) from user, 返回值为: Long
     * select * from user 返回值为: User
     * select count(*) cnt, sum(age) sum_age 返回值为: getOperation()+"Result";
     * </pre>
     * @return
     */
    public String getResultClass() {
        if(StringHelper.isNotBlank(resultClass)) return resultClass;
        if(columns.size() == 1) {
            return columns.iterator().next().getSimpleJavaType();
        }
        if(isColumnsInSameTable()) {
            return columns.iterator().next().getTable().getClassName();
        }else {
            if(operation == null) return null;
            return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(operation))+"Result";
        }
    }

    public void setResultClass(String queryResultClass) {
        this.resultClass = queryResultClass;
    }
    /**
     * 返回getResultClass()的类名称 <br />
     * 示例: <br />
     * 如getResultClass()=com.company.User,将返回User
     */
    public String getResultClassName() {
        int lastIndexOf = getResultClass().lastIndexOf(".");
        return lastIndexOf >= 0 ? getResultClass().substring(lastIndexOf+1) : getResultClass();
    }

    /**
     * SQL参数过多时用于封装为一个ParameterObject的class<br />
     * <pre>
     * 可以通过setParameterClass()自定义
     * 没有自定义则:
     * 如果是select查询,返回 operation+"Query"
     * 其它则返回operation+"Parameter"
     * <pre>
     */
    public String getParameterClass() {
        if(StringHelper.isNotBlank(parameterClass)) return parameterClass;
        if(StringHelper.isBlank(operation)) return null;
        if(isSelectSql()) {
            return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(operation))+"Query";
        }else {
            return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(operation))+"Parameter";
        }
    }

    public void setParameterClass(String parameterClass) {
        this.parameterClass = parameterClass;
    }
    /**
     * 返回getParameterClass()的类名称 <br />
     * 示例: <br />
     * 如getParameterClass()=com.company.UserQuery,将返回UserQuery
     */
    public String getParameterClassName() {
        int lastIndexOf = getParameterClass().lastIndexOf(".");
        return lastIndexOf >= 0 ? getParameterClass().substring(lastIndexOf+1) : getParameterClass();
    }

    //TODO columnsSize大于二并且不是在同一张表中,将创建一个QueryResultClassName类,同一张表中也要考虑创建类
    public int getColumnsCount() {
        return columns.size();
    }
    public void addColumn(Column c) {
        columns.add(c);
    }
    /**
     * 得到该sql方法相对应的操作名称,模板中的使用方式为: public List ${operation}(),示例值: findByUsername
     * @return
     */
    public String getOperation() {
        return operation;
    }
    public void setOperation(String operation) {
        this.operation = operation;
    }
    public String getOperationFirstUpper() {
        return StringHelper.capitalize(getOperation());
    }
    /**
     * 用于控制查询结果,固定值为:one,many
     * @return
     */
    public String getMultiplicity() {
        return multiplicity;
    }
    public void setMultiplicity(String multiplicity) {
        //TODO 是否要增加验证数据为 one,many
        this.multiplicity = multiplicity;
    }
    /**
     * 得到sqlect 查询的列对象(column),如果是insert,delete,update语句,则返回empty Set.<br />
     * 示例:
     * <pre>
     * SQL : select count(*) cnt, sum(age) sum_age from user_info
     * columns: cnt,sum_age
     * </pre>
     * @return
     */
    public LinkedHashSet<Column> getColumns() {
        return columns;
    }
    public void setColumns(LinkedHashSet<Column> columns) {
        this.columns = columns;
    }
    /**
     * 得到SQL的参数对象<br />
     * 示例:
     * <pre>
     * SQL : select * from user_info where username=:user and password=:pwd limit :offset,:limit
     * params: user,pwd,offset,limit
     * </pre>
     * @return
     */
    public LinkedHashSet<SqlParameter> getParams() {
        return params;
    }
    public void setParams(LinkedHashSet<SqlParameter> params) {
        this.params = params;
    }
    public SqlParameter getParam(String paramName) {
        for(SqlParameter p : getParams()) {
            if(p.getParamName().equals(paramName)) {
                return p;
            }
        }
        return null;
    }
    /**
     * 得到SQL原始语句
     * @return
     */
    public String getSourceSql() {
        return sourceSql;
    }
    public void setSourceSql(String sourceSql) {
        this.sourceSql = sourceSql;
    }

    public String getSqlmap() {
        return sqlmap;
    }

    public void setSqlmap(String sqlmap) {
        this.sqlmap = sqlmap;
    }

    //	public String replaceParamsWith(String prefix,String suffix) {
//		String sql = sourceSql;
//		List<SqlParameter> sortedParams = new ArrayList(params);
//		Collections.sort(sortedParams,new Comparator<SqlParameter>() {
//			public int compare(SqlParameter o1, SqlParameter o2) {
//				return o2.paramName.length() - o1.paramName.length();
//			}
//		});
//		for(SqlParameter s : sortedParams){ //FIXME 现在只实现了:username参数替换
//			sql = StringHelper.replace(sql,":"+s.getParamName(),prefix+s.getParamName()+suffix);
//		}
//		return sql;
//	}
    /**
     * sourceSql转换为在数据库实际执行的SQL,
     * 示例:
     * <pre>
     * sourceSql: select * from user where username=:username and password=:password
     * executeSql: select * from user where username=? and password=?
     * </pre>
     * @return
     */
    public String getExecuteSql() {
        return executeSql;
    }

    public void setExecuteSql(String executeSql) {
        this.executeSql = executeSql;
    }

    public String getCountHql() {
        if(isSelectSql()) {
            return countQueryPrefix + SqlParseHelper.removeSelect(getHql());
        }else {
            return getHql();
        }
    }

    private String countQueryPrefix = "select count(*) ";
    public String getCountSql() {
        if(isSelectSql()) {
            return countQueryPrefix + SqlParseHelper.removeSelect(getSql());
        }else {
            return getSql();
        }
    }

    public String getIbatisCountSql() {
        if(isSelectSql()) {
            return countQueryPrefix + SqlParseHelper.removeSelect(getIbatisSql());
        }else {
            return getIbatisSql();
        }
    }

    public String getIbatis3CountSql() {
        if(isSelectSql()) {
            return countQueryPrefix + SqlParseHelper.removeSelect(getIbatis3Sql());
        }else {
            return getIbatis3Sql();
        }
    }

    public String getSql() {
        return replaceWildcardWithColumnsSqlName(sourceSql);
    }

    public String getSpringJdbcSql() {
        return SqlParseHelper.convert2NamedParametersSql(getSql(),":","");
    }

    public String getHql() {
        return SqlParseHelper.convert2NamedParametersSql(getSql(),":","");
    }

    public String getIbatisSql() {
        return StringHelper.isBlank(ibatisSql) ? SqlParseHelper.convert2NamedParametersSql(getSql(),"#","#") : ibatisSql;
    }

    public String getIbatis3Sql() {
        return StringHelper.isBlank(ibatis3Sql) ? SqlParseHelper.convert2NamedParametersSql(getSql(),"#{","}") : ibatis3Sql;
    }

    public void setIbatisSql(String ibatisSql) {
        this.ibatisSql = ibatisSql;
    }

    public void setIbatis3Sql(String ibatis3Sql) {
        this.ibatis3Sql = ibatis3Sql;
    }

    private String joinColumnsSqlName() {
        // TODO 未解决 a.*,b.*问题
        StringBuffer sb = new StringBuffer();
        for(Iterator<Column> it = columns.iterator();it.hasNext();) {
            Column c = it.next();
            sb.append(c.getSqlName());
            if(it.hasNext()) sb.append(",");
        }
        return sb.toString();
    }

    public String replaceWildcardWithColumnsSqlName(String sql) {
        if(isSelectSql() && SqlParseHelper.getSelect(sql).indexOf("*") >= 0 && SqlParseHelper.getSelect(sql).indexOf("count(") < 0) {
            return SqlParseHelper.getPrettySql("select " + joinColumnsSqlName() + " " + SqlParseHelper.removeSelect(sql));
        }else {
            return sql;
        }
    }

    /**
     * 当前的sourceSql是否是select语句
     * @return
     */
    public boolean isSelectSql() {
        return sourceSql.trim().toLowerCase().matches("(?is)\\s*select\\s.*from\\s+.*");
    }
    /**
     * 当前的sourceSql是否是update语句
     * @return
     */
    public boolean isUpdateSql() {
        return sourceSql.trim().toLowerCase().matches("(?is)\\s*update\\s+.*");
    }
    /**
     * 当前的sourceSql是否是delete语句
     * @return
     */
    public boolean isDeleteSql() {
        return sourceSql.trim().toLowerCase().matches("(?is)\\s*delete\\s+from\\s.*");
    }
    /**
     * 当前的sourceSql是否是insert语句
     * @return
     */
    public boolean isInsertSql() {
        return sourceSql.trim().toLowerCase().matches("(?is)\\s*insert\\s+into\\s+.*");
    }
    /**
     * 得到表相对应的sqlName,主要用途为生成文件时的分组.
     * @return
     */
    public String getTableSqlName() {
        return tableSqlName;
    }

    public void setTableSqlName(String tableName) {
        this.tableSqlName = tableName;
    }
    /**
     * 得到备注
     * @return
     */
    public String getRemarks() {
        return remarks;
    }

    public void setRemarks(String comments) {
        this.remarks = comments;
    }

    public boolean isPaging() {
        if(MULTIPLICITY_PAGING.equalsIgnoreCase(multiplicity)) {
            return true;
        }
        return paging;
    }

    public void setPaging(boolean paging) {
        this.paging = paging;
    }

    /**
     * 根据tableSqlName和成相对应的tableClassName,主要用途路径变量引用.如${tableClassName}Dao.java
     * @return
     */
    public String getTableClassName() {
        if(StringHelper.isBlank(tableSqlName)) return null;
        String removedPrefixSqlName = Table.removeTableSqlNamePrefix(tableSqlName);
        return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(removedPrefixSqlName));
    }

    public Column getColumnBySqlName(String sqlName) {
        for(Column c : getColumns()) {
            if(c.getSqlName().equalsIgnoreCase(sqlName)) {
                return c;
            }
        }
        return null;
    }

    public Column getColumnByName(String name) {
        Column c = getColumnBySqlName(name);
        if(c == null) {
            c = getColumnBySqlName(StringHelper.toUnderscoreName(name));
        }
        return c;
    }

    public String toString() {
        return "sourceSql:\n"+sourceSql+"\nsql:"+getSql();
    }

    private String ibatisSql;
    private String ibatis3Sql;
}